﻿using Excel2DbTool.Common;
using Excel2DbTool.Helpers;
using Excel2DbTool.Models;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;

namespace Excel2DbTool.Migrater_New
{

    public class CTableMigrater : SimpleDbTableMigrater
    {
        public static readonly string PrimeKey = SelfFieldNames.CID;
        public static readonly string GROUPKEY = OrginTableFieldNames.LAYER;


        static List<DbFieldMapping> dbFieldMappings = new List<DbFieldMapping>()
        {
            //has match
            new OracleTableNumField(OrginTableFieldNames.LAYER, SelfFieldNames.SJC),
            //new OracleTableDateTimeField(OrginTableFieldNames.SAVEDATE,SelfFieldNames.CJSJ),
            //new OracleTableDateTimeField(OrginTableFieldNames.MODIFYTIME,SelfFieldNames.XGSJ),

            //has match ,but not only one
            new OracleTableStrFieldReferToOtherFieldInSameTable(OrginTableFieldNames.NONE,SelfFieldNames.MYC, SelfFieldNames.SJC,(sjc)=>{
                return sjc;
            }),

             new OracleTableStrFieldHasFixedVal(OrginTableFieldNames.NONE,SelfFieldNames.SFTSC,"0"),
            //can not be null,but orgin table has no match；so we will not select relative data from orgin table;
            //but will give a default value to  target table
            new OracleTableIdentityStrField(OrginTableFieldNames.NONE,SelfFieldNames.CID), //KEY

            new OracleTableReferenceKeyField(OrginTableFieldNames.NONE,SelfFieldNames.ZRZID),
            new OracleTableReferenceKeyField(OrginTableFieldNames.NONE,SelfFieldNames.LJZID),
            new OracleTableReferenceKeyField(OrginTableFieldNames.NONE,SelfFieldNames.XMID),

        };

        IList<DbRowMappingNew> ljz_dbrms;
        DbRowMappingNew zrz_dbrm;
        DbRowMappingNew xm_dbrm;
        public CTableMigrater(IList<DbRowMappingNew> ljz_dbrms, DbRowMappingNew zrz_dbrm, DbRowMappingNew xm_dbrm)
        {
            if (ljz_dbrms == null || zrz_dbrm == null || xm_dbrm == null)
            {
                throw new ArgumentNullException();
            }

            this.ljz_dbrms = ljz_dbrms;
            this.zrz_dbrm = zrz_dbrm;
            this.xm_dbrm = xm_dbrm;
        }

        protected override IList<DbFieldMapping> DbFieldMappings
        {
            get { return dbFieldMappings; }
        }


        IList<DbRowMappingNew> dbRowMappings;

        public void Execute(Action<string> transactionInsertAction)
        {
            object xm_projectNameVal = xm_dbrm.GetValByTargetFieldName(XMTableMigrater.PrimeKey);

            foreach (var ljz_dbrm in ljz_dbrms)
            {
                dbRowMappings = new List<DbRowMappingNew>();

                object val_ljzPrimiteKey = ljz_dbrm.GetValByTargetFieldName(LJZTableMigrater.PrimeKey);

                string sql = GetSelectSqlStr(ljz_dbrm);

                var dt = SqlHelper.GetInstance().Select(sql);

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    DbRowMappingNew dbrm = GetDbRowMapping(val_ljzPrimiteKey);

                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        dbrm[j].Val = dt.Rows[i][j];
                    }

                    dbRowMappings.Add(dbrm);
                }

                string insertSql = GetInsertSqlString();
                transactionInsertAction(insertSql);

                HTableMigrater h = new HTableMigrater(dbRowMappings, ljz_dbrm, zrz_dbrm, xm_dbrm);
                h.Execute(transactionInsertAction);
            }
        }

        string GetSelectSqlStr(DbRowMappingNew ljz_dbrm)
        {
            object xm_projectNameVal = xm_dbrm.GetValByOrginFieldName(XMTableMigrater.GROUPKEY);
            object zrz_buildNoVal = zrz_dbrm.GetValByOrginFieldName(ZRZTableMigrater.GROUPKEY);
            object ljz_buildNoVal = ljz_dbrm.GetValByOrginFieldName(LJZTableMigrater.GROUPKEY);

            string predicateStr = string.Format("{0} = {1} AND {2} = {3} AND {4} = {5}", ZRZTableMigrater.GROUPKEY, zrz_buildNoVal, XMTableMigrater.GROUPKEY, xm_projectNameVal, LJZTableMigrater.GROUPKEY, ljz_buildNoVal);
            string format = "SELECT {0} FROM {1} where rowid = any (select max(rowid) from (select * from {1} where {2}) group by {3}) ";

            string sql = string.Format(format, GetFieldStringNeedReslove(), ConfigHelper.TableName, predicateStr, GROUPKEY);

            return sql;
        }


        string GetInsertSqlString()
        {
            if (dbRowMappings.Count == 0)
            {
                return string.Empty;
            }

            StringBuilder sb = new StringBuilder(20000);
            sb.AppendFormat("INSERT INTO {0} ({1})", DbTableNames.BDC_C, GetFieldStringNeedInsert());


            for (int i = 0; i < dbRowMappings.Count(); i++)
            {
                sb.AppendFormat(" SELECT {0} FROM dual {1}", dbRowMappings.ElementAt(i).GetValStr(), (i == dbRowMappings.Count() - 1) ? "" : "union");
            }

            return sb.ToString();
        }

        private DbRowMappingNew GetDbRowMapping(object val_ljzPrimiteKey)
        {
            DbRowMappingNew dbrm = new DbRowMappingNew();

            foreach (var item in dbFieldMappings)
            {
                if (item is OracleTableReferenceKeyField)
                {
                    if (item.TargetFieldName == SelfFieldNames.XMID)
                    {
                        item.Val = xm_dbrm.GetValByTargetFieldName(SelfFieldNames.XMID);
                    }
                    else if (item.TargetFieldName == SelfFieldNames.ZRZID)
                    {
                        item.Val = zrz_dbrm.GetValByTargetFieldName(SelfFieldNames.ZRZID);
                    }
                    else
                    {
                        //LJZID
                        item.Val = val_ljzPrimiteKey;
                    }

                }
                dbrm.AddFieldMapping(item.Clone());
            }

            return dbrm;
        }

        class SelfFieldNames
        {
            internal static readonly string CID = "CID";
            internal static readonly string ZRZID = "ZRZID";
            internal static readonly string LJZID = "LJZID";
            internal static readonly string SJC = "SJC";
            internal static readonly string MYC = "MYC";
            internal static readonly string CYCJZMJ = "CYCJZMJ";
            internal static readonly string CSCJZMJ = "CSCJZMJ";
            internal static readonly string CTNJZMJ = "CTNJZMJ";
            internal static readonly string CYTMJ = "CYTMJ";
            internal static readonly string CGYJZMJ = "CGYJZMJ";
            internal static readonly string CFTJZMJ = "CFTJZMJ";
            internal static readonly string CBQMJ = "CBQMJ";
            internal static readonly string CG = "CG";
            internal static readonly string SPTYMJ = "SPTYMJ";
            internal static readonly string CJSJ = "CJSJ";
            internal static readonly string CJR = "CJR";
            internal static readonly string XGSJ = "XGSJ";
            internal static readonly string XGR = "XGR";
            internal static readonly string XMID = "XMID";
            internal static readonly string SFTSC = "SFTSC";
        }
    }


    /*
       -- Create table
    create table BDC_C
    (
      cid     VARCHAR2(40) not null,
      zrzid   VARCHAR2(36) not null,
      ljzid   VARCHAR2(36) not null,
      sjc     INTEGER,
      myc     VARCHAR2(50),
      cycjzmj FLOAT,
      cscjzmj FLOAT,
      ctnjzmj FLOAT,
      cytmj   FLOAT,
      cgyjzmj FLOAT,
      cftjzmj FLOAT,
      cbqmj   FLOAT,
      cg      FLOAT,
      sptymj  FLOAT,
      cjsj    DATE,
      cjr     VARCHAR2(50),
      xgsj    DATE,
      xgr     VARCHAR2(50),
      xmid    VARCHAR2(36),
      sftsc   INTEGER
    )
    tablespace BDCDJ0616
      pctfree 10
      initrans 1
      maxtrans 255
      storage
      (
        initial 128K
        next 8K
        minextents 1
        maxextents unlimited
      );
    -- Add comments to the columns 
    comment on column BDC_C.cid
      is '层ID';
    comment on column BDC_C.zrzid
      is '自然幢ID';
    comment on column BDC_C.ljzid
      is '逻辑幢ID';
    comment on column BDC_C.sjc
      is '实际层';
    comment on column BDC_C.myc
      is '名义层';
    comment on column BDC_C.cycjzmj
      is '层预测建筑面积';
    comment on column BDC_C.cscjzmj
      is '层实测建筑面积';
    comment on column BDC_C.ctnjzmj
      is '层套内建筑面积';
    comment on column BDC_C.cytmj
      is '层阳台面积';
    comment on column BDC_C.cgyjzmj
      is '层共有建筑面积';
    comment on column BDC_C.cftjzmj
      is '层分摊建筑面积';
    comment on column BDC_C.cbqmj
      is '层半墙面积';
    comment on column BDC_C.cg
      is '层高';
    comment on column BDC_C.sptymj
      is '水平投影面积';
    comment on column BDC_C.cjsj
      is '创建时间';
    comment on column BDC_C.cjr
      is '创建人';
    comment on column BDC_C.xgsj
      is '修改时间';
    comment on column BDC_C.xgr
      is '修改人';
    comment on column BDC_C.xmid
      is '项目id';
    comment on column BDC_C.sftsc
      is '是否特殊层 否：0    是：1';
    -- Create/Recreate primary, unique and foreign key constraints 
    alter table BDC_C
      add constraint N_BDC_C_CID_PRI primary key (CID)
      using index 
      tablespace BDCDJ0616
      pctfree 10
      initrans 2
      maxtrans 255
      storage
      (
        initial 64K
        next 1M
        minextents 1
        maxextents unlimited
      );

     */

}
